﻿Imports QuanLyBanChau.Chau_bus
Imports QuanLyBanChau.LoaiChau_bus
Imports System.Runtime.InteropServices
Imports System.Data.OleDb

Public Class frmQuanLyChau
    Private Sub frmQuanLyChau_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        LoadData()
    End Sub

    Public Sub LoadData()
        Dim dt As DataTable = New DataTable()
        dt = Get_chauBUS()
        gvDanhSachChau.DataSource = dt

        dt = Get_LoaiChauBUS()
        CbxMaloai.DataSource = dt
        CbxMaloai.DisplayMember = "Maloai"
        CbxMaloai.ValueMember = "Maloai"
    End Sub
    Private Sub btnThem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnThem.Click
        Dim Chau As Chau = New Chau()
        Chau.Machau = TxtMachau.Text.ToString()
        Chau.Kichthuoc = TxtKichthuot.Text
        Chau.Maloai = CbxMaloai.Text
        Chau.Soluong = TxtSoluong.Text
        Chau.Tenchau = TxtTenchau.Text
        Chau.Trigia = TxtTrigia.Text
        Insert_ChauBUS(Chau)
        frmQuanLyChau_Load(sender, e)
    End Sub

    Private Sub gvDanhSachChau_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles gvDanhSachChau.CellClick
        TxtMachau.Text = gvDanhSachChau.CurrentRow.Cells(0).Value.ToString()
        TxtTenchau.Text = gvDanhSachChau.CurrentRow.Cells(1).Value.ToString()
        CbxMaloai.Text = gvDanhSachChau.CurrentRow.Cells(2).Value.ToString()
        TxtTrigia.Text = gvDanhSachChau.CurrentRow.Cells(3).Value.ToString()
        TxtKichthuot.Text = gvDanhSachChau.CurrentRow.Cells(4).Value.ToString()
        TxtSoluong.Text = gvDanhSachChau.CurrentRow.Cells(5).Value.ToString()
    End Sub

    Private Sub btnXoa_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnXoa.Click
        Dim XacNhan As DialogResult = MessageBox.Show("Bạn có chắc chắn muốn xóa dòng này không?", "Xóa?", MessageBoxButtons.OKCancel, MessageBoxIcon.Question)
        If XacNhan = DialogResult.OK Then
            Dim maChau As String
            maChau = gvDanhSachChau.CurrentRow.Cells(0).Value.ToString()
            Delete_ChauBUS(maChau)
            gvDanhSachChau.Rows.Remove(gvDanhSachChau.CurrentRow)
        End If
    End Sub

    Private Sub btnSua_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSua.Click
        Dim Chau As Chau = New Chau()
        Chau.Machau = TxtMachau.Text.ToString()
        Chau.Kichthuoc = TxtKichthuot.Text
        Chau.Maloai = CbxMaloai.Text
        Chau.Soluong = TxtSoluong.Text
        Chau.Tenchau = TxtTenchau.Text
        Chau.Trigia = TxtTrigia.Text
        Update_ChauBUS(Chau)
        frmQuanLyChau_Load(sender, e)

    End Sub

    Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
        Dim excel As New ExportToExcel()
        Dim dt As DataTable = DirectCast(gvDanhSachChau.DataSource, DataTable)
        excel.Export(dt, "Danh sach", "DANH SÁCH CÁC CHẬU")
    End Sub

    Private Sub btnimport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnimport.Click
        Dim ofd As New OpenFileDialog()

        Dim path As String = ""


        If ofd.ShowDialog() = DialogResult.OK Then
            path = System.IO.Path.GetFullPath(ofd.FileName)

            Dim ExcelObj As New Microsoft.Office.Interop.Excel.Application()

            Dim theWorkbook As Microsoft.Office.Interop.Excel.Workbook = Nothing


            Dim strPath As String = path


            theWorkbook = ExcelObj.Workbooks.Open(strPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _
             Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _
             Type.Missing, Type.Missing, Type.Missing)

            Dim sheets As Microsoft.Office.Interop.Excel.Sheets = theWorkbook.Worksheets

            Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = DirectCast(sheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet)
            'Get the reference of second worksheet
            Dim strWorksheetName As String = worksheet.Name
            'Get the name of worksheet.
            Dim conn As New OleDbConnection()
            conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = '" & path & "'" & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0"""
            Dim a As String = "SELECT * FROM [" & strWorksheetName & "$]"
            Dim command As New OleDbCommand(a, conn)

            Dim projectactivityDS As New DataSet()

            Dim adapter As New OleDbDataAdapter(command)

            adapter.Fill(projectactivityDS)

            gvDanhSachChau.DataSource = projectactivityDS.Tables(0)
            theWorkbook.Close()
            ExcelObj.Quit()
            conn.Close()


            ' Call the garbage collector to collect and wait for finalizers to finish.
            GC.Collect()
            GC.WaitForPendingFinalizers()

            ' Release the COM objects that have been instantiated.
            While System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets) <> 0
            End While
            GC.Collect()
            GC.WaitForPendingFinalizers()
            Marshal.FinalReleaseComObject(theWorkbook)
            Marshal.FinalReleaseComObject(worksheet)
            Marshal.FinalReleaseComObject(sheets)
            Marshal.FinalReleaseComObject(ExcelObj)
        End If
    End Sub
End Class